﻿	SELECT 
	 TOP 10 REGION_U
	,STATE
	--,WIRE_CENTER
	,PROPERTY_ID_U
	,NATIONAL_IND_U
	,DWELLING_TYPE_U
	--,ADDRESS_U
	--,UNIT  AS UNIT_U
	--,CITY_NM_U
	,REGION_DESC_U
	--,SEGMENT_U
	,PROPERTY_NAME_U
	,AM_U
	,AE_ASSIGNED_U
	,PORTFOLIO_NAME_U
	--,PR_STATUS_U
	--,C_STATUS_U
	,MARKET_AREA_U
	--,DA_U
	--,WCDA_U
	--,EVENT_ID_U
	--,SONBR_U
	--,DISC_RSN_CD_U
	--,DISC_RSN_U   
	--,SALES_CODE_U
	--,DIST_CHANNEL_U
	--,BAN_U
	--,ORDER_TYPE_U
	--,SOURCE_CD_U
	--,POSTDATE_U
	,PRODUCT_ID_U
	--,PRODUCT_NM_U       
	--,INWARD_OUTWARD_CD_U         
	,PRODUCT_ACTION_CD_U  
	,PROD_QTY_U
	 FROM
	( -- TOT
		SELECT  
		--CASE  STATE 
		--   WHEN  'IL'  THEN 'A'
		--   WHEN  'IN'  THEN 'A'
		--   WHEN  'MI'  THEN 'A'
		--   WHEN  'WI'  THEN 'A'
		--   WHEN  'OH'  THEN 'A'
		--   WHEN  'CA'  THEN 'P'
		--   WHEN  'NV'  THEN 'P'
		--   WHEN  'CT'  THEN 'N'
		--   WHEN  'TX'  THEN 'S'
		--   WHEN  'KS'  THEN 'S'
		--   WHEN  'AR'  THEN 'S'
		--   WHEN  'OK'  THEN 'S'
		--   WHEN  'MO'  THEN 'S'
		--   ELSE 'U'
		--       END  AS  REGION_U
		STATE     /*   ADDED 9/9/09 PER MIST REQUEST  126821   */
		--,WIRE_CENTER
		,LEGS.PROPERTY_ID AS PROPERTY_ID_U
		,NATIONAL_IND_U
		,DWELLING_TYPE_U
		,ADDRESS  AS ADDRESS_U
		--,UNIT
		--,CITY_NM AS CITY_NM_U
		--,REGION AS REGION_DESC_U
		,PROPERTY_NAME  AS PROPERTY_NAME_U
		,AM  AS AM_U
		,AE_ASSIGNED  AS AE_ASSIGNED_U
		,PORTFOLIO_NAME  AS PORTFOLIO_NAME_U
		--,PR_STATUS  AS PR_STATUS_U
		--,C_STATUS  AS C_STATUS_U
		,MARKET_AREA  AS MARKET_AREA_U
		--,DISTRIBUTION_AREA AS DA_U
		--,TRIM(WIRE_CENTER)||TRIM(DISTRIBUTION_AREA)  AS WCDA_U
		--,BASE.EVENT_ID  AS EVENT_ID_U
		--,SONBR  AS SONBR_U
		--,DISC_RSN_CD_U
		--,DISC_RSN_U   
		--,BASE.SALES_CODE AS SALES_CODE_U
		--,COALESCE(DIST_CHANNEL,SALES_CHANNEL_CD,'UNKNOWN')  AS DIST_CHANNEL_U
		--,BAN AS BAN_U
		--,ORDER_TYPE  AS ORDER_TYPE_U
		--,SOURCE_CD  AS SOURCE_CD_U
		,POSTDATE  AS POSTDATE_U
		,LEGS.PRODUCT_ID   AS PRODUCT_ID_U
		--,PRODUCT_NM           AS PRODUCT_NM_U       
		--,INWARD_OUTWARD_CD    AS INWARD_OUTWARD_CD_U         
		,PRODUCT_ACTION_CD    AS PRODUCT_ACTION_CD_U  
		,PROD_QTY AS PROD_QTY_U
		FROM
		( -- LEGS
			SELECT  
			PROPERTY_ID 
			--,ADDRESS
			--,UNIT
			,REGION
			,PROPERTY_NAME
			,NATIONAL_IND  AS  NATIONAL_IND_U
			,DWELLING_TYPE  AS DWELLING_TYPE_U
			,AM
			,AE_ASSIGNED
			,PORTFOLIO_NAME
			--,PR_STATUS
			--,C_STATUS
			,MARKET_AREA
			--,DISTRIBUTION_AREA
			--,CHK.EVENT_ID
			--,SONBR
			--,DISC_RSN_CD_U
			--,DISC_RSN_U   
			--,SALES_CODE
			--,SALES_CHANNEL_CD
			--,BAN
			--,ORDER_TYPE
			--,SOURCE_CD
			,POSTDATE
			,PRODUCT_ID   
			--,INWARD_OUTWARD_CD             
			,PRODUCT_ACTION_CD             
			,PROD_QTY  
			--,CITY_NM  
			,STATE   
			FROM
			( -- CHK
				SELECT 
				S.PROPERTY_ID 
				--,S.ADDRESS
				--,UNIT
				--,SC.REGION
				,SC.PROPERTY_NAME
				,SC.NATIONAL_IND
				,SC.DWELLING_TYPE
				,SC.AM
				,SC.AE_ASSIGNED
				,SC.PORTFOLIO_NAME
				--,SC.PR_STATUS
				--,SC.C_STATUS
				,SC.MARKET_AREA
				--,' ' AS DISTRIBUTION_AREA
				--,E.RAAID
				--,E.EVENT_ID
				--,E.SONBR
				--,DISC_RSN_CD_U
				--,DISC_RSN_U   
				--,SALES_CODE
				--,SALES_CHANNEL_CD
				--,BAN
				--,ORDER_TYPE
				--,SOURCE_CD
				,E.POSTDATE
				--,E.CONTACT_NM
				--,E.CITY_NM
				,E.STATE
				--,E.ZIP_CD
				FROM 
				SMARTMOVES.TXSM012_SMOVES_ADDRESSES  
				S
				,
				SMARTMOVES.TXSM101_SO_SCORE 	
				SC
  				,
				(--E
					SELECT 
					-- RAAID
					--,EVENT_ID
					--,SONBR
					--,DISC_RSN_CD_U
					--,DISC_RSN_U   
					--,SALES_CODE
					--,SALES_CHANNEL_CD
					--,BAN
					--,ORDER_TYPE
					--,SOURCE_CD
					POSTDATE
					--,CONTACT_NM
					--,CITY_NM
					--,ADDRESS
					--,UNIT
					,STATE
					--,ZIP_CD
					FROM
					(--BASE
						SELECT	
						--REPOSITORY_AFFILIATE_ACCT_ID RAAID
						--,EVENT_ID
						--,SONBR
						--,DISC_RSN_CD_U
						--,DISC_RSN_U   
						--,SALES_CODE
						--,SALES_CHANNEL_CD
						--,BAN
						--,ORDER_TYPE
						--,SOURCE_CD
						POSTDATE
						--,ADDRESS_ID
						--,CONTACT_NM
						--,CITY_NM
						--,PRIMARY_ADDRESS_TXT
						--,SECONDARY_ADDRESS_TXT AS  UNIT
						--,TRIM(C.SECONDARY_ADDRESS_TXT) (NAMED UNIT_ADD)     
						--,CASE WHEN C.STREET_DIRECTION_PREFIX_TXT > ' '      
						--THEN TRIM(C.STREET_DIRECTION_PREFIX_TXT) ||' '||
						--TRIM(C.STREET_NM)                          
						--ELSE C.STREET_NM                                
						--END (NAMED ADR1)                                
						--,CASE WHEN C.ADDRESS_HOUSE_NBR > ' '                
						--THEN TRIM(C.ADDRESS_HOUSE_NBR) ||' '|| TRIM(ADR1)    
						--ELSE ADR1                                            
						--END (NAMED ADR2)                                     
						--,CASE WHEN C.ADDRESS_SUFFIX_TYPE_CD > ' '                
						--THEN TRIM(ADR2) ||' '|| TRIM(C.ADDRESS_SUFFIX_TYPE_CD)
						--ELSE ADR2                                            
						--END (NAMED ADR3)                                     
						--,CASE WHEN C.STREET_DIRECTION_SUFFIX_TXT > ' '           
						--THEN TRIM(ADR3) ||' '||                              
						--TRIM(C.STREET_DIRECTION_SUFFIX_TXT)             
						--ELSE ADR3                                            
						--END (NAMED ADDRESS)      
						--,C.ZIP_CD
						,C.TERRITORY_CD AS STATE
						FROM  
						ENTERPRISE_RETAIL_VIEWS.VCCG559_ACCOUNT_CNTCT_GEO_ADDR 
						C  
						,
						(--S
							SELECT 
							-- H205.REPOSITORY_AFFILIATE_ACCT_ID  RAAID  
							--,H205.EVENT_ID
							--,H205.SERVICE_ORDER_NBR        		SONBR
							--,DISC_RSN_CD_U
							--,DISC_RSN_U     
							--,O.BAN
							H205.EVENT_POSTED_DT    			POSTDATE
							--,H205.EVENT_COMPLETION_DT    		CMPLDATE 
							--,H205.EVENT_CLASS_CD				ORDER_TYPE
							--,H205.DATA_SOURCE_CD				SOURCE_CD
							--,SUBSTR(H205.ORIGINATING_SALES_CD,1,7) AS SALES_CODE     
							--,CASE WHEN O.SALES_CHANNEL_CD   = 'WEB' THEN 'ONLINE' ELSE O.SALES_CHANNEL_CD END AS SALES_CHANNEL_CD            
							FROM
							ENTERPRISE_RETAIL_VIEWS.VCCH205_SERVICE_ORDER_EVENT 
							H205
							,
							( -- O
								SELECT 
								--I.ORDER_ID	
								--,I.SERVICE_ORDER_CD
								--,I.BAN
								--,SALES_CHANNEL_CD
								--,DISC_RSN_CD_U     
								--,DISC_RSN_U
								FROM	  
								TELCO_UNREG_RETAIL_VIEWS.VCTV800_ORDER	
								I					
								LEFT OUTER JOIN 
								(--D
									SELECT 
									--A.ORDER_ID
									--,A.ORDER_ACTION_TYPE_CD
									--,A.ORDER_ACTION_REASON_CD   DISC_RSN_CD_U
									--,C.ORDER_ACTION_REASON_DESC DISC_RSN_U
									FROM
									TELCO_UNREG_RETAIL_VIEWS.VCTV801_ORDER_ACTION  	 
									A 
									,
									TELCO_UNREG_RETAIL_VIEWS.VCTV804_ORDER_ACTION_RSN_DESC  
									C
									WHERE  
									A.ORDER_ACTION_REASON_CD = C.ORDER_ACTION_REASON_CD
									AND	A.ORDER_ACTION_TYPE_CD = C.ORDER_ACTION_TYPE_CD
									AND A.ORDER_ACTION_REVISION_TYPE_CD = C.ORDER_ACTION_REVISION_TYPE_CD
									AND	A.CURRENT_ORDER_ACTION_STATUS_CD = 'DO'
									AND A.ORDER_ACTION_TYPE_CD IN ('CH', 'CE') 
								)
								D
								ON I.ORDER_ID = D.ORDER_ID
							) 	
							O												
							WHERE  
							H205.EVENT_POSTED_DT = DATE-4 -----------------------
							AND H205.DATA_SOURCE_CD IN (150)
							AND O.ORDER_ID = SONBR
							AND RAAID > 0
							GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12   
						) 
						S
						WHERE 
						C.REPOSITORY_AFFILIATE_ACCT_ID = S.RAAID
						AND C.ZIP_CD <> ' '
						AND C.ADDRESS_USAGE_CD = 'S' 
					) 
					BASE
					GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
				) 
				E
				WHERE 
				TRIM(S.ADDRESS) = TRIM(E.ADDRESS)
				AND S.ZIP = E.ZIP_CD
				AND S.PROPERTY_ID = SC.PROPERTY_ID
				AND (SC.PR_STATUS = 'ACTIVE' AND C_STATUS = 'ACTIVE' ) 
			) 
			CHK
			LEFT OUTER JOIN 
			(--ACT
				SELECT 
				 --D.EVENT_ID
				 D.PRODUCT_ID                    
				--,D.INWARD_OUTWARD_CD             
				,CASE WHEN INWARD_OUTWARD_CD = 1 THEN 'I' ELSE 'O' END AS PRODUCT_ACTION_CD             
				,SUM(D.PRODUCT_QTY)    PROD_QTY                  
				FROM 
				ENTERPRISE_RETAIL_VIEWS.VCCH207_SVC_ORD_PROD_EVENT  
				D
				WHERE  
				D.DATA_SOURCE_CD IN (150)
				AND D.INWARD_OUTWARD_CD  IN (1,4)
				AND D.PRODUCT_ID > 0
				GROUP BY 1,2,3,4
			) 
			ACT
			ON CHK.EVENT_ID = ACT.EVENT_ID
		) 
		LEGS
		LEFT OUTER JOIN
		(--P
			SELECT 
			--PRODUCT_ID
			--,PRODUCT_NM
			FROM
			ENTERPRISE_RETAIL_VIEWS.VCCR200_PRODUCT   
			WHERE 
			PRODUCT_LAST_EFFECTIVE_DT > DATE
			AND PRODUCT_ID > 0
			GROUP BY 1,2
		)  
		P
		ON LEGS.PRODUCT_ID = P.PRODUCT_ID
		LEFT OUTER JOIN
		(--S
			SELECT 
			 --SALES_CODE
			--,CHANNEL  AS DIST_CHANNEL
			FROM
			MIS.SALES_CHANNEL_LOOKUP
			GROUP BY 1,2
		) 
		S
		ON LEGS.SALES_CODE = S.SALES_CODE
		LEFT OUTER JOIN
/*   ADDED 9/9/09 PER MIST REQUEST  126821   */
		(--W
			SELECT 
			 --PROPERTY_ID
			--,WIRE_CENTER
			FROM SMARTMOVES.TXSM011_SMOVES_PROPERTIES    
			GROUP BY 1,2
		) 
		W
		ON LEGS.PROPERTY_ID = W.PROPERTY_ID
		WHERE PRODUCT_ACTION_CD IN ('I','O')
		GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34
	) 
	TOT
/*  ADDED PER  MIST  131152: Determine/Develop Process for CLLI-DA Tracking for ACC- Uverse    */
	LEFT OUTER JOIN
	(	
		SELECT WCDA
		,SEGMENT AS SEGMENT_U
		FROM SPECIAL_TABLES.DG8331_ABCD_SEGS
		GROUP BY 1,2
	) 
	SEG
	ON TOT.WCDA_U = SEG.WCDA
	GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35;
/*  UVERSE ORDERS 
1/19/2010  CHANGED SPECIAL_TABLES.DG8331 TO 
SMARTMOVES.TXSM101_SO_SCORE AND DIST_CHANNEL SOURCE
TO CHANNEL FROM  CHANNEL_PARTNER   */
